<?php	
//一对多【多存少的】
//班级class
cid	cname
1	67		
2   68
3   69
4	70
//学生表stu
sid name 		cid
1	张三		 	 1
2	李四		 	 2
3	刘超群		 3
4	张国宝		 3
5	王国宝		 3
6	宋慈		 	 4

//笛卡儿积
select * from stu,class;


//（练习一）查找张国宝所在的班级
	//第一种方法
	select cid from stu where name="张国宝";
	+-----+
	| cid |
	+-----+
	|   3 |
	+-----+
	select cname from class where cid=3;
	//第二种方法
	select c.cname from stu s join class c on s.cid=c.cid where s.name="张国宝";
	
//（练习二）王国宝的同班同学
//第一种方法
//1.找到王国宝在那个班【班级cid】
select cid from stu where name="王国宝";
+-----+
| cid |
+-----+
|   3 |
+-----+
select name from stu where cid=3 and name !="王国宝";

//子查询
select name from stu where cid=(select cid from stu where name="王国宝") and name !="王国宝";

//自关联--第二种方法【建议使用该方法】
//1.先进行关联
select s1.sid,s1.name,s2.sid,s2.name from stu s1 join stu s2 on s1.cid=s2.cid;
//2.找s1.name="王国宝"
select s1.sid,s1.name,s2.sid,s2.name from stu s1 join stu s2 on s1.cid=s2.cid where s1.name="王国宝";
//3.朝朝s2.name的数据
select s2.name from stu s1 join stu s2 on s1.cid=s2.cid where s1.name="王国宝";
//4.王国宝自己排除
select s2.name from stu s1 join stu s2 on s1.cid=s2.cid where s1.name="王国宝" and s2.name!="王国宝";
select count(*) from student s join class c join s.cid=c.cid where group by c.cid;
//左关联
select * from stu s left join class c on s.cid = c.cid;
//右关联
select * from stu s right join class c on s.cid = c.cid;




























